{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reader and Writer Packages"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## OpenPyXL\n",
    "### Reading with OpenPyXL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import openpyxl\n",
    "import excel\n",
    "import datetime as dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Open the workbook to read cell values.\n",
    "# The file is automatically closed again after loading the data.\n",
    "book = openpyxl.load_workbook(\"xl/stores.xlsx\", data_only=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get a worksheet object by name or index (0-based)\n",
    "sheet = book[\"2019\"]\n",
    "sheet = book.worksheets[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get a list with all sheet names\n",
    "book.sheetnames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Loop through the sheet objects.\n",
    "# Instead of \"name\", openpyxl uses \"title\".\n",
    "for i in book.worksheets:\n",
    "    print(i.title)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Getting the dimensions,\n",
    "# i.e. the used range of the sheet\n",
    "sheet.max_row, sheet.max_column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the value of a single cell\n",
    "# using \"A1\" notation and using cell indices (1-based)\n",
    "sheet[\"B6\"].value\n",
    "sheet.cell(row=6, column=2).value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in a range of cell values by using our excel module\n",
    "data = excel.read(book[\"2019\"], (2, 2), (8, 6))\n",
    "data[:2]  # Print the first two rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Writing with OpenPyXL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "from openpyxl.drawing.image import Image\n",
    "from openpyxl.chart import BarChart, Reference\n",
    "from openpyxl.styles import Font, colors\n",
    "from openpyxl.styles.borders import Border, Side\n",
    "from openpyxl.styles.alignment import Alignment\n",
    "from openpyxl.styles.fills import PatternFill\n",
    "import excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Instantiate a workbook\n",
    "book = openpyxl.Workbook()\n",
    "\n",
    "# Get the first sheet and give it a name\n",
    "sheet = book.active\n",
    "sheet.title = \"Sheet1\"\n",
    "\n",
    "# Writing individual cells using A1 notation\n",
    "# and cell indices (1-based)\n",
    "sheet[\"A1\"].value = \"Hello 1\"\n",
    "sheet.cell(row=2, column=1, value=\"Hello 2\")\n",
    "\n",
    "# Formatting: fill color, alignment, border and font\n",
    "font_format = Font(color=\"FF0000\", bold=True)\n",
    "thin = Side(border_style=\"thin\", color=\"FF0000\")\n",
    "sheet[\"A3\"].value = \"Hello 3\"\n",
    "sheet[\"A3\"].font = font_format\n",
    "sheet[\"A3\"].border = Border(top=thin, left=thin,\n",
    "                            right=thin, bottom=thin)\n",
    "sheet[\"A3\"].alignment = Alignment(horizontal=\"center\")\n",
    "sheet[\"A3\"].fill = PatternFill(fgColor=\"FFFF00\", fill_type=\"solid\")\n",
    "\n",
    "# Number formatting (using Excel's formatting strings)\n",
    "sheet[\"A4\"].value = 3.3333\n",
    "sheet[\"A4\"].number_format = \"0.00\"\n",
    "\n",
    "# Date formatting (using Excel's formatting strings)\n",
    "sheet[\"A5\"].value = dt.date(2016, 10, 13)\n",
    "sheet[\"A5\"].number_format = \"mm/dd/yy\"\n",
    "\n",
    "# Formula: you must use the English name of the formula\n",
    "# with commas as delimiters\n",
    "sheet[\"A6\"].value = \"=SUM(A4, 2)\"\n",
    "\n",
    "# Image\n",
    "sheet.add_image(Image(\"images/python.png\"), \"C1\")\n",
    "\n",
    "# Two-dimensional list (we're using our excel module)\n",
    "data = [[None, \"North\", \"South\"],\n",
    "        [\"Last Year\", 2, 5],\n",
    "        [\"This Year\", 3, 6]]\n",
    "excel.write(sheet, data, \"A10\")\n",
    "\n",
    "# Chart\n",
    "chart = BarChart()\n",
    "chart.type = \"col\"\n",
    "chart.title = \"Sales Per Region\"\n",
    "chart.x_axis.title = \"Regions\"\n",
    "chart.y_axis.title = \"Sales\"\n",
    "chart_data = Reference(sheet, min_row=11, min_col=1,\n",
    "                       max_row=12, max_col=3)\n",
    "chart_categories = Reference(sheet, min_row=10, min_col=2,\n",
    "                             max_row=10, max_col=3)\n",
    "# from_rows interprets the data in the same way\n",
    "# as if you would add a chart manually in Excel\n",
    "chart.add_data(chart_data, titles_from_data=True, from_rows=True)\n",
    "chart.set_categories(chart_categories)\n",
    "sheet.add_chart(chart, \"A15\")\n",
    "\n",
    "# Saving the workbook creates the file on disk\n",
    "book.save(\"openpyxl.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "book = openpyxl.Workbook()\n",
    "sheet = book.active\n",
    "sheet[\"A1\"].value = \"This is a template\"\n",
    "book.template = True\n",
    "book.save(\"template.xltx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Editing with OpenPyXL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the stores.xlsx file, change a cell\n",
    "# and store it under a new location/name.\n",
    "book = openpyxl.load_workbook(\"xl/stores.xlsx\")\n",
    "book[\"2019\"][\"A1\"].value = \"modified\"\n",
    "book.save(\"stores_edited.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "book = openpyxl.load_workbook(\"xl/macro.xlsm\", keep_vba=True)\n",
    "book[\"Sheet1\"][\"A1\"].value = \"Click the button!\"\n",
    "book.save(\"macro_openpyxl.xlsm\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## XlsxWriter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime as dt\n",
    "import xlsxwriter\n",
    "import excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Instantiate a workbook\n",
    "book = xlsxwriter.Workbook(\"xlxswriter.xlsx\")\n",
    "\n",
    "# Add a sheet and give it a name\n",
    "sheet = book.add_worksheet(\"Sheet1\")\n",
    "\n",
    "# Writing individual cells using A1 notation\n",
    "# and cell indices (0-based)\n",
    "sheet.write(\"A1\", \"Hello 1\")\n",
    "sheet.write(1, 0, \"Hello 2\")\n",
    "\n",
    "# Formatting: fill color, alignment, border and font\n",
    "formatting = book.add_format({\"font_color\": \"#FF0000\",\n",
    "                              \"bg_color\": \"#FFFF00\",\n",
    "                              \"bold\": True, \"align\": \"center\",\n",
    "                              \"border\": 1, \"border_color\": \"#FF0000\"})\n",
    "sheet.write(\"A3\", \"Hello 3\", formatting)\n",
    "\n",
    "# Number formatting (using Excel's formatting strings)\n",
    "number_format = book.add_format({\"num_format\": \"0.00\"})\n",
    "sheet.write(\"A4\", 3.3333, number_format)\n",
    "\n",
    "# Date formatting (using Excel's formatting strings)\n",
    "date_format = book.add_format({\"num_format\": \"mm/dd/yy\"})\n",
    "sheet.write(\"A5\", dt.date(2016, 10, 13), date_format)\n",
    "\n",
    "# Formula: you must use the English name of the formula\n",
    "# with commas as delimiters\n",
    "sheet.write(\"A6\", \"=SUM(A4, 2)\")\n",
    "\n",
    "# Image\n",
    "sheet.insert_image(0, 2, \"images/python.png\")\n",
    "\n",
    "# Two-dimensional list (we're using our excel module)\n",
    "data = [[None, \"North\", \"South\"],\n",
    "        [\"Last Year\", 2, 5],\n",
    "        [\"This Year\", 3, 6]]\n",
    "excel.write(sheet, data, \"A10\")\n",
    "\n",
    "# Chart: see the file \"sales_report_xlsxwriter.py\" in the\n",
    "# companion repo to see how you can work with indices\n",
    "# instead of cell addresses\n",
    "chart = book.add_chart({\"type\": \"column\"})\n",
    "chart.set_title({\"name\": \"Sales per Region\"})\n",
    "chart.add_series({\"name\": \"=Sheet1!A11\",\n",
    "                  \"categories\": \"=Sheet1!B10:C10\",\n",
    "                  \"values\": \"=Sheet1!B11:C11\"})\n",
    "chart.add_series({\"name\": \"=Sheet1!A12\",\n",
    "                  \"categories\": \"=Sheet1!B10:C10\",\n",
    "                  \"values\": \"=Sheet1!B12:C12\"})\n",
    "chart.set_x_axis({\"name\": \"Regions\"})\n",
    "chart.set_y_axis({\"name\": \"Sales\"})\n",
    "sheet.insert_chart(\"A15\", chart)\n",
    "\n",
    "# Closing the workbook creates the file on disk\n",
    "book.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "book = xlsxwriter.Workbook(\"macro_xlxswriter.xlsm\")\n",
    "sheet = book.add_worksheet(\"Sheet1\")\n",
    "sheet.write(\"A1\", \"Click the button!\")\n",
    "book.add_vba_project(\"xl/vbaProject.bin\")\n",
    "sheet.insert_button(\"A3\", {\"macro\": \"Hello\", \"caption\": \"Button 1\",\n",
    "                           \"width\": 130, \"height\": 35})\n",
    "book.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## pyxlsb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyxlsb\n",
    "import excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Loop through sheets. With pyxlsb, the workbook\n",
    "# and sheet objects can be used as context managers.\n",
    "# book.sheets returns a list of sheet names, not objects!\n",
    "# To get a sheet object, use get_sheet() instead.\n",
    "with pyxlsb.open_workbook(\"xl/stores.xlsb\") as book:\n",
    "    for sheet_name in book.sheets:\n",
    "        with book.get_sheet(sheet_name) as sheet:\n",
    "            dim = sheet.dimension\n",
    "            print(f\"Sheet '{sheet_name}' has \" \n",
    "                  f\"{dim.h} rows and {dim.w} cols\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in the values of a range of cells by using our excel module.\n",
    "# Instead of \"2019\", you could also use its index (1-based).\n",
    "with pyxlsb.open_workbook(\"xl/stores.xlsb\") as book:\n",
    "    with book.get_sheet(\"2019\") as sheet:\n",
    "        data = excel.read(sheet, \"B2\")\n",
    "data[:2]  # Print the first two rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyxlsb import convert_date\n",
    "convert_date(data[1][3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(\"xl/stores.xlsb\", engine=\"pyxlsb\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## xlrd, xlwt and xlutils"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading with xlrd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlrd\n",
    "import xlwt\n",
    "from xlwt.Utils import cell_to_rowcol2\n",
    "import xlutils\n",
    "import excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Open the workbook to read cell values. The file is\n",
    "# automatically closed again after loading the data.\n",
    "book = xlrd.open_workbook(\"xl/stores.xls\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get a list with all sheet names\n",
    "book.sheet_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Loop through the sheet objects\n",
    "for sheet in book.sheets():\n",
    "    print(sheet.name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get a sheet object by name or index (0-based)\n",
    "sheet = book.sheet_by_index(0)\n",
    "sheet = book.sheet_by_name(\"2019\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dimensions\n",
    "sheet.nrows, sheet.ncols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the value of a single cell\n",
    "# using \"A1\" notation and using cell indices (0-based).\n",
    "# The \"*\" unpacks the tuple that cell_to_rowcol2 returns \n",
    "# into individual arguments.\n",
    "sheet.cell(*cell_to_rowcol2(\"B3\")).value\n",
    "sheet.cell(2, 1).value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in a range of cell values by using our excel module\n",
    "data = excel.read(sheet, \"B2\")\n",
    "data[:2]  # Print the first two rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Writing with xlwt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlwt\n",
    "from xlwt.Utils import cell_to_rowcol2\n",
    "import datetime as dt\n",
    "import excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Instantiate a workbook\n",
    "book = xlwt.Workbook()\n",
    "\n",
    "# Add a sheet and give it a name\n",
    "sheet = book.add_sheet(\"Sheet1\")\n",
    "\n",
    "# Writing individual cells using A1 notation\n",
    "# and cell indices (0-based)\n",
    "sheet.write(*cell_to_rowcol2(\"A1\"), \"Hello 1\")\n",
    "sheet.write(r=1, c=0, label=\"Hello 2\")\n",
    "\n",
    "# Formatting: fill color, alignment, border and font\n",
    "formatting = xlwt.easyxf(\"font: bold on, color red;\"\n",
    "                         \"align: horiz center;\"\n",
    "                         \"borders: top_color red, bottom_color red,\"\n",
    "                                  \"right_color red, left_color red,\"\n",
    "                                  \"left thin, right thin,\"\n",
    "                                  \"top thin, bottom thin;\"\n",
    "                         \"pattern: pattern solid, fore_color yellow;\")\n",
    "sheet.write(r=2, c=0, label=\"Hello 3\", style=formatting)\n",
    "\n",
    "# Number formatting (using Excel's formatting strings)\n",
    "number_format = xlwt.easyxf(num_format_str=\"0.00\")\n",
    "sheet.write(3, 0, 3.3333, number_format)\n",
    "\n",
    "# Date formatting (using Excel's formatting strings)\n",
    "date_format = xlwt.easyxf(num_format_str=\"mm/dd/yyyy\")\n",
    "sheet.write(4, 0, dt.datetime(2012, 2, 3), date_format)\n",
    "\n",
    "# Formula: you must use the English name of the formula\n",
    "# with commas as delimiters\n",
    "sheet.write(5, 0, xlwt.Formula(\"SUM(A4, 2)\"))\n",
    "\n",
    "# Two-dimensional list (we're using our excel module)\n",
    "data = [[None, \"North\", \"South\"],\n",
    "        [\"Last Year\", 2, 5],\n",
    "        [\"This Year\", 3, 6]]\n",
    "excel.write(sheet, data, \"A10\")\n",
    "\n",
    "# Picture (only allows to add bmp format)\n",
    "sheet.insert_bitmap(\"images/python.bmp\", 0, 2)\n",
    "\n",
    "# This writes the file to disk\n",
    "book.save(\"xlwt.xls\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Editing with xlutils"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlutils.copy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "book = xlrd.open_workbook(\"xl/stores.xls\", formatting_info=True)\n",
    "book = xlutils.copy.copy(book)\n",
    "book.get_sheet(0).write(0, 0, \"changed!\")\n",
    "book.save(\"stores_edited.xls\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Advanced Topics\n",
    "## Working with Big Files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Writing with OpenPyXL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "book = openpyxl.Workbook(write_only=True)\n",
    "# With write_only=True, book.active doesn't work\n",
    "sheet = book.create_sheet()\n",
    "# This will produce a sheet with 1000 x 200 cells\n",
    "for row in range(1000):\n",
    "    sheet.append(list(range(200)))\n",
    "book.save(\"openpyxl_optimized.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Writing with XlsxWriter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "book = xlsxwriter.Workbook(\"xlsxwriter_optimized.xlsx\",\n",
    "                           options={\"constant_memory\": True})\n",
    "sheet = book.add_worksheet()\n",
    "# This will produce a sheet with 1000 x 200 cells\n",
    "for row in range(1000):\n",
    "    sheet.write_row(row , 0, list(range(200)))\n",
    "book.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading with xlrd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with xlrd.open_workbook(\"xl/stores.xls\", on_demand=True) as book:\n",
    "    sheet = book.sheet_by_index(0)  # Only loads the first sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with xlrd.open_workbook(\"xl/stores.xls\", on_demand=True) as book:\n",
    "    with pd.ExcelFile(book, engine=\"xlrd\") as f:\n",
    "        df = pd.read_excel(f, sheet_name=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading with OpenPyXL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "book = openpyxl.load_workbook(\"xl/big.xlsx\",\n",
    "                              data_only=True, read_only=True,\n",
    "                              keep_links=False)\n",
    "# Perform the desired read operations here\n",
    "book.close()  # Required with read_only=True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading in Parallel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "data = pd.read_excel(\"xl/big.xlsx\",\n",
    "                     sheet_name=None, engine=\"openpyxl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "import parallel_pandas\n",
    "data = parallel_pandas.read_excel(\"xl/big.xlsx\", sheet_name=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Formatting DataFrames in Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelFile(\"xl/stores.xlsx\", engine=\"openpyxl\") as xlfile:\n",
    "    # Read a DataFrame\n",
    "    df = pd.read_excel(xlfile, sheet_name=\"2020\")\n",
    "\n",
    "    # Get the OpenPyXL workbook object\n",
    "    book = xlfile.book\n",
    "\n",
    "    # From here on, it's OpenPyXL code\n",
    "    sheet = book[\"2019\"]\n",
    "    value = sheet[\"B3\"].value  # Read a single value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter(\"pandas_and_openpyxl.xlsx\",\n",
    "                    engine=\"openpyxl\") as writer:\n",
    "    df = pd.DataFrame({\"col1\": [1, 2, 3, 4], \"col2\": [5, 6, 7, 8]})\n",
    "    # Write a DataFrame\n",
    "    df.to_excel(writer, \"Sheet1\", startrow=4, startcol=2)\n",
    "\n",
    "    # Get the OpenPyXL workbook and sheet objects\n",
    "    book = writer.book\n",
    "    sheet = writer.sheets[\"Sheet1\"]\n",
    "\n",
    "    # From here on, it's OpenPyXL code\n",
    "    sheet[\"A1\"].value = \"This is a Title\"  # Write a single cell value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({\"col1\": [1, -2], \"col2\": [-3, 4]},\n",
    "                   index=[\"row1\", \"row2\"])\n",
    "df.index.name = \"ix\"\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl.styles import PatternFill"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter(\"formatting_openpyxl.xlsx\",\n",
    "                    engine=\"openpyxl\") as writer:\n",
    "    # Write out the df with the default formatting to A1\n",
    "    df.to_excel(writer, startrow=0, startcol=0)\n",
    "\n",
    "    # Write out the df with custom index/header formatting to A6    \n",
    "    startrow, startcol = 0, 5\n",
    "    # 1. Write out the data part of the DataFrame\n",
    "    df.to_excel(writer, header=False, index=False,\n",
    "                startrow=startrow + 1, startcol=startcol + 1)\n",
    "    # Get the sheet object and create a style object\n",
    "    sheet = writer.sheets[\"Sheet1\"]\n",
    "    style = PatternFill(fgColor=\"D9D9D9\", fill_type=\"solid\")\n",
    "\n",
    "    # 2. Write out the styled column headers\n",
    "    for i, col in enumerate(df.columns):\n",
    "        sheet.cell(row=startrow + 1, column=i + startcol + 2,\n",
    "                   value=col).fill = style\n",
    "\n",
    "    # 3. Write out the styled index\n",
    "    index = [df.index.name if df.index.name else None] + list(df.index)\n",
    "    for i, row in enumerate(index):\n",
    "        sheet.cell(row=i + startrow + 1, column=startcol + 1,\n",
    "                   value=row).fill = style"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Formatting index/headers with XlsxWriter\n",
    "with pd.ExcelWriter(\"formatting_xlsxwriter.xlsx\",\n",
    "                    engine=\"xlsxwriter\") as writer:\n",
    "    # Write out the df with the default formatting to A1\n",
    "    df.to_excel(writer, startrow=0, startcol=0)\n",
    "\n",
    "    # Write out the df with custom index/header formatting to A6\n",
    "    startrow, startcol = 0, 5\n",
    "    # 1. Write out the data part of the DataFrame\n",
    "    df.to_excel(writer, header=False, index=False,\n",
    "                startrow=startrow + 1, startcol=startcol + 1)\n",
    "    # Get the book and sheet object and create a style object\n",
    "    book = writer.book\n",
    "    sheet = writer.sheets[\"Sheet1\"]\n",
    "    style = book.add_format({\"bg_color\": \"#D9D9D9\"})\n",
    "\n",
    "    # 2. Write out the styled column headers\n",
    "    for i, col in enumerate(df.columns):\n",
    "        sheet.write(startrow, startcol + i + 1, col, style)\n",
    "\n",
    "    # 3. Write out the styled index\n",
    "    index = [df.index.name if df.index.name else None] + list(df.index)\n",
    "    for i, row in enumerate(index):\n",
    "        sheet.write(startrow + i, startcol, row, style)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl.styles import Alignment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter(\"data_format_openpyxl.xlsx\",\n",
    "                    engine=\"openpyxl\") as writer:\n",
    "    # Write out the DataFrame\n",
    "    df.to_excel(writer)\n",
    "    \n",
    "    # Get the book and sheet objects\n",
    "    book = writer.book\n",
    "    sheet = writer.sheets[\"Sheet1\"]\n",
    "    \n",
    "    # Formatting individual cells\n",
    "    nrows, ncols = df.shape\n",
    "    for row in range(nrows):\n",
    "        for col in range(ncols):\n",
    "            # +1 to account for the header/index\n",
    "            # +1 since OpenPyXL is 1-based\n",
    "            cell = sheet.cell(row=row + 2,\n",
    "                              column=col + 2)\n",
    "            cell.number_format = \"0.000\"\n",
    "            cell.alignment = Alignment(horizontal=\"center\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter(\"data_format_xlsxwriter.xlsx\",\n",
    "                    engine=\"xlsxwriter\") as writer:\n",
    "    # Write out the DataFrame\n",
    "    df.to_excel(writer)\n",
    "\n",
    "    # Get the book and sheet objects    \n",
    "    book = writer.book\n",
    "    sheet = writer.sheets[\"Sheet1\"]\n",
    "    \n",
    "    # Formatting the columns (individual cells can't be formatted)\n",
    "    number_format = book.add_format({\"num_format\": \"0.000\",\n",
    "                                     \"align\": \"center\"})\n",
    "    sheet.set_column(first_col=1, last_col=2,\n",
    "                     cell_format=number_format)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.style.applymap(lambda x: \"number-format: 0.000;\"\n",
    "                            \"text-align: center\")\\\n",
    "        .to_excel(\"styled.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({\"Date\": [dt.date(2020, 1, 1)],\n",
    "                   \"Datetime\": [dt.datetime(2020, 1, 1, 10)]})\n",
    "with pd.ExcelWriter(\"date.xlsx\",\n",
    "                    date_format=\"yyyy-mm-dd\",\n",
    "                    datetime_format=\"yyyy-mm-dd hh:mm:ss\") as writer:\n",
    "    df.to_excel(writer)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
